# -*- coding: utf-8 -*-
"""
python与sqlite数据库的交互。
python自带sqlite模块，可以创建sqlite数据库.
数据库入库,只要将全部数据转成字符串格式，sqlite会自动转成int，float，date等格式，和mysql差不多。
这里使用了数据库连接池.
使用方法：
import pandas as pd
from tools.database import pysqlite
sqlite = pysqlite.sqlite()  #实例化
sql = "select 1"
pd.read_sql(sql,con=sqlite.conn)
sqlite.close()  # 用完要关闭连接
"""

import sqlite3
# from DBUtils.PooledDB import PooledDB

# conn = sqlite3.connect(sqlitedb)

# cur=conn.cursor()
# cur.execute()
#
# sqlite3.threadsafety()



# from config import config
# sqlitedb = config.g_var.sqlitedb
sqlitedb = r"E:\stock_py\stock_py\tools\database\sqlitedb.db"


class GSqlite:
    """数据库连接池"""
    pool = None


class sqlite_conn(object):
    """python操作sqllite数据库的类"""

    def __init__(self, db=sqlitedb):
        self.db = db
        self.conn = sqlite_conn.connect(self)
        self.cursor = self.conn.cursor()

    def connect(self):
        # if GSqlite.pool == None:
        #     GSqlite.pool = PooledDB(sqlite3, mincached=1, db=self.db,check_same_thread = False)
        # return GSqlite.pool.connection()
        conn = sqlite3.connect(database=self.db)
        return conn

    def execute(self, sql):
        self.conn.execute(sql)
        self.conn.commit()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def df_into_df(self, table, df, types='insert'):
        # 将df全部转成字符串格式，注意是否有空
        if len(df) == 0:
            return None
        to_str = lambda s: str(s) if s else''
        df = df.applymap(to_str)
        # 创建导数据的sql
        columns = ','.join(list(df.columns))
        sql = "insert into limit_table (columns) values ( ?_times ) "
        values = ['?' for i in range(len(columns))]
        values = ','.join(values)
        sql = sql.replace('limit_table', table)
        sql = sql.replace('columns', columns)
        sql = sql.replace('?_times', values)
        # 判断是否需要清空表
        if types.upper() in "INSERT,TRUNCATE":
            self.cursor.execute("delete from %s" % table)
            self.conn.commit()
        # 将df转成list-tuple
        param = []
        for i in range(len(df)):
            param.append(tuple(df.iloc[i]))
        # 入库
        self.cursor.executemany(sql, param)
        self.conn.commit()


def main():
    pass


if __name__ == '__main__':
    main()
